文章目录
inner join(join)关键字outer joinunion
inner join(join)关键字
用于将多个表联合显示出来 除了之前的customers表来表示客户,同样有一张order表示客户订单,如下:![在这里插入图片描述](https://img-blog.csdnimg.cn/20200415213421232.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3ODYzODkx,size_16,color_FFFFFF,t_70)
以某一列为值联合
select *
from orders
inner join customers //为内部联合,inner关键字可省略
on orders.customer_id = customers.customer_id //只联合customer_id相等的数据
联合数据库中的其它表
select order_id, o.customer_id,first_name,last_name //对于两个都有的customer_id要指明主表
from orders o //令order简称为o
join customers c//令customers简称为c
on o.customer_id = c.customer_id
联合其它数据库中的表
select *
from order_items oi
join sql_inventory.products p //需要为不是当前数据库的表加上前缀,前缀为其他数据库别名
on oi.product_id = p.product_id
联合自身 这种情况是为了一张表内关联数据的对比来显示,例如以下一张员工表 员工要向经理汇报(reports_to),而经理本身来说也是一名员工有自己的员工id,将每个员工向经理汇报的数据显示出来
select e.employee_id,e.first_name,m.first_name as manager
from employees e
join employees m //注意不同别名
on e.reports_to = m.employee_id
联合两张及以上的表格 在例子中将三张表order,customer,order_statuses联合起来显示有用的数据方便观察
USE sql_store;
select
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name as status
from orders o
join customers c
on o.customer_id = c.customer_id
join order_statuses os
on o.status = os.order_status_id
执行显示图案如下,信息显示十分清楚,多张表的联合类似 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200416183009626.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3ODYzODkx,size_16,color_FFFFFF,t_70)
对于复合主键的join 如下图 无论是order还是product_id都不能标识唯一一行数据,在表中以这两个键值做为复合主键来标识唯一数据,可以看到黄色的记号,一般表明为主键(一般为第一列),可以根据主键查找唯一数据,而这张表将order_id与product_id做为复合主键来查找数据。
use sql_store;
select *
from order_item_notes oin
join order_items oi
on oi.order_id = oin.order_id
and oi.product_id = oin.product_id //复合主键的联合
outer join
有名为外部联机,有时候会出现以下情况。有两张表customer,order
order customer 通过如下代码
SELECT
c.customer_id,
c.first_name,
o.order_id
from customers c
join orders o
on c.customer_id = o.customer_id
order by c.customer_id
此时显示的只有条件匹配的数据,但是想要无论order中有没有该客户,都要显示出来,就要用到外部联合
外部联合left join与right join 无论on的条件是否成立,使用left join联合时前面的表都能全部显示。使用right join联合时后面的表都全部显示。
SELECT
c.customer_id,
c.first_name,
o.order_id
from customers c
left (outer) join orders o //outer可以省略
on c.customer_id = o.customer_id
order by c.customer_id
此时能够显示所有的客户id,尽管没有order_id
外部联合的多表联合
SELECT
c.customer_id,
c.first_name,
o.order_id
sh.name as shipper
from customers c
left (outer) join orders o //outer可以省略
on c.customer_id = o.customer_id
left join shippers sh
on o.shipper_id = sh.shipper_id
order by c.customer_id
尽量使用单一的left或者rignt,不要混合使用
self outer join 例如之前的内部自身联合的员工表,对其进行下修改
select
e.employee_id,
e.first_name,
m.first_name as manager
from employees e
left join employees m //注意不同别名
on e.reports_to = m.employee_id
manager的员工编号能够显示出来。
使用using代替join中的on,只有在两个判断条件键值同名时才可以使用
SELECT
c.customer_id,
c.first_name,
o.order_id
sh.name as shipper
from customers c
left (outer) join orders o //outer可以省略
//on c.customer_id = o.customer_id
using(cuntomer_id) //效果等同
left join shippers sh
//on o.shipper_id = sh.shipper_id
using(shipper_id )
order by c.customer_id
using在复合主键的使用
use sql_store;
select *
from order_item_notes oin
join order_items oi
//on oi.order_id = oin.order_id and oi.product_id = oin.product_id
using(order_id ,product_id)
natural join(不建议使用)
select
o.order_id
c.first_name
from orders o
natural join customers c //基于两个表的公共列,因此在大量的表中不可控,不建议使用
cross join 将第一张表中的每行与第二张表中的每行组合,所以一共显示的行数为 table1行数 * table2行数
select
c.first_name as customer,
p.name as product
from customers c
cross join products p
//from customers c,orders o 隐式写法
order by c.first_name
union
将两次查询的结构合并出来显示,可以在同一个表内也可以在不同的表里
selec first_name //注意选择的列数的数量要一致
from customers
union
select name
from shipper
以customer为例,points在2000以下的打上青铜标签,2000-3000之间的打上白银标签,3000以上的打开黄金标签
use sql_store;
select customer_id, first_name, points, "Bronze" as type
from customers
where points 3000
结果如下 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200416211622610.png)
|